﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Library
{
    public class clDH
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public DataTable getdata()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_loaidh_danhsach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable getDH(Int16 type)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_dh_danhsach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@type", SqlDbType.SmallInt).Value = type;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable chitiet(Int16 id)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_dh_chitiet", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.SmallInt).Value = id;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int updateDH(Int64 id, Int64 idkh, string madh, Int16 idloaiDH, string kichthuoc, Int64 ngaylap, Int16 idtolap, string tentolap, Int16 trangthai,Nullable<int> chisoDH,Nullable<float> tongdutoan,string ctdutoan,Nullable<int> cscDHtruoc,Nullable<int> csmDHtruoc,Nullable<int> cscDhmoi,string motalapdat)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_dh_capnhat", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@madh", SqlDbType.NVarChar, 50).Value = madh;
                    sqlcmd.Parameters.Add("@idloai", SqlDbType.SmallInt).Value = idloaiDH;
                    sqlcmd.Parameters.Add("@kichthuoc", SqlDbType.NVarChar, 50).Value = kichthuoc;
                    sqlcmd.Parameters.Add("@ngaylap", SqlDbType.BigInt).Value = ngaylap;
                    sqlcmd.Parameters.Add("@idtolap", SqlDbType.SmallInt).Value = idtolap;
                    sqlcmd.Parameters.Add("@tentolap", SqlDbType.NVarChar, 250).Value = tentolap;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = trangthai;
                    sqlcmd.Parameters.Add("@chisoDH", SqlDbType.Int).Value = chisoDH;
                    sqlcmd.Parameters.Add("@tongdutoan", SqlDbType.Float).Value = tongdutoan;
                    sqlcmd.Parameters.Add("@chitietdutoan", SqlDbType.NVarChar,4000).Value = ctdutoan;
                    sqlcmd.Parameters.Add("@cscDH1", SqlDbType.Int).Value = cscDhmoi;
                    sqlcmd.Parameters.Add("@csmDH1", SqlDbType.Int).Value = csmDHtruoc;
                    sqlcmd.Parameters.Add("@cscDH2", SqlDbType.Int).Value = cscDhmoi;
                    sqlcmd.Parameters.Add("@motalapdat", SqlDbType.NVarChar,250).Value = motalapdat;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public DataTable gettolap()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_totc_danhsach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int add(string ten, string ghichu)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_loaidh_themmoi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@ten", SqlDbType.NVarChar, 75).Value = ten;
                    sqlcmd.Parameters.Add("@ghichu", SqlDbType.NVarChar, 500).Value = ghichu;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int thaydongdo(Int64 idkh,string madh,Int16 idloai,string kichthuoc,Int64 ngaylap,Int16 idtolap,string tolap,Nullable<int> csmDH1,Nullable<int> cscDH2,string chitietdutoan,Nullable<float> tongdutoan,string motaLapDat)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_KhachHang_thayDongHo", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@madh", SqlDbType.NVarChar, 50).Value = madh;
                    sqlcmd.Parameters.Add("@idloai", SqlDbType.SmallInt).Value = idloai;
                    sqlcmd.Parameters.Add("@kichthuoc", SqlDbType.NVarChar, 50).Value = kichthuoc;
                    sqlcmd.Parameters.Add("@ngaylap", SqlDbType.BigInt).Value = ngaylap;
                    sqlcmd.Parameters.Add("@idtolap", SqlDbType.SmallInt).Value = idtolap;
                    sqlcmd.Parameters.Add("@tolap", SqlDbType.NVarChar, 250).Value = tolap;
                    sqlcmd.Parameters.Add("@csmDH1", SqlDbType.Int).Value = csmDH1;
                    sqlcmd.Parameters.Add("@cscDH2", SqlDbType.Int).Value = cscDH2;
                    sqlcmd.Parameters.Add("@dutoan", SqlDbType.NVarChar).Value = chitietdutoan;
                    sqlcmd.Parameters.Add("@tongdutoan", SqlDbType.Float).Value = tongdutoan;
                    sqlcmd.Parameters.Add("@mota", SqlDbType.NVarChar,250).Value = motaLapDat;
                    //sqlcmd.Parameters.Add("@ngaydoc", SqlDbType.BigInt).Value = ngaydoc;
                    //sqlcmd.Parameters.Add("@nvten", SqlDbType.NVarChar, 50).Value = nvten;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public DataTable DongHoSDQuaHan(Int16 nam)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_dongho_5nam", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@nam", SqlDbType.SmallInt).Value = nam;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable LichSuDongHoKH(string makh)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_lichsu_dongho_khachhang", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@makh", SqlDbType.NVarChar,50).Value = makh;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

    }
}
